<?php

namespace app\api\controller;

use app\admin\library\Auth;
use app\api\model\Gclass;
use app\api\model\Grade;
use app\api\model\School;
use app\api\model\Student;
use app\api\model\Students;
use app\api\model\Teacher;
use app\common\controller\Api;
use app\common\model\User;
use Exception;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Reader\Csv;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use think\Db;
use think\exception\PDOException;
use think\Request;
use think\Validate;
use app\api\controller\Communal;
use app\common\model\UserRole;
use fast\Random;

/**
 * 人员信息
 */
class People extends Api
{

    protected $noNeedLogin = ['*'];
    protected $noNeedRight = ['*'];
    protected $importHeadType = 'comment';
    protected $status = ['normal' => '正常', 'hidden' => '关闭'];
    protected $tag = ['1' => '复读生', '2' => '借读生', '3' => '艺术生', '4' => '体育生', '5' => '学生'];
    protected $classType = ['XZ' => '行政班', 'JX' => '教学班'];

    //学生列表
    public function getStudentList()
    {
        if (!$this->request->isPost()) $this->error('请求方式错误！');
        $school_id = $this->school_id;
        $name = $this->request->post('name');
        $parent_phone = $this->request->post('parent_phone');
        $exam_number = $this->request->post('exam_number');
        $grade_id = $this->request->post('grade_id');
        $class_id = $this->request->post('class_id');
        $tag = $this->request->post('tag');
        $select_exam_id = $this->request->post('select_exam_id');
        $page = $this->request->post('page');
        $pagesize = $this->request->post('pagesize');
        if (!$page || !$pagesize) $this->error('参数错误！');
        $list = db('student')
            ->where(function ($query) use ($name) {
                if ($name) {
                    $query->where('name', 'like', '%' . $name . '%');
                }
            })
            ->where(function ($query) use ($grade_id) {
                if ($grade_id) {
                    $query->where('grade_id', $grade_id);
                }
            })
            ->where(function ($query) use ($parent_phone) {
                if ($parent_phone) {
                    $query->where('parent_phone', $parent_phone);
                }
            })
            ->where(function ($query) use ($exam_number) {
                if ($exam_number) {
                    $query->where('exam_number', $exam_number);
                }
            })
            ->where(function ($query) use ($class_id) {
                if ($class_id) {
                    $query->where('class_id', $class_id);
                }
            })
            ->where(function ($query) use ($tag) {
                if ($tag) {
                    $query->where('tag', $tag);
                }
            })
            ->where(function ($query) use ($select_exam_id) {
                if ($select_exam_id) {
                    $query->where('select_exam_id', $select_exam_id);
                }
            })
            ->where(['status' => 'normal', 'school_id' => $school_id])->order('id desc')->paginate($pagesize, false)->each(function ($item, $index) {
                $select_exam = db('select_exam')->where('id', $item['select_exam_id'])->value('name');
                $elective_arr = [];
                if($item['chinese_class']){
                    array_push($elective_arr,$item['chinese_class']);
                }
                if($item['elective_ids']){
                    array_push($elective_arr,$item['elective_ids']);
                }
                if($item['maths_class']){
                    array_push($elective_arr,$item['maths_class']);
                }
                if($item['physics_class']){
                    array_push($elective_arr,$item['physics_class']);
                }
                if($item['chemistry_class']){
                    array_push($elective_arr,$item['chemistry_class']);
                }
                if($item['organism_class']){
                    array_push($elective_arr,$item['organism_class']);
                }
                if($item['politics_class']){
                    array_push($elective_arr,$item['politics_class']);
                }
                if($item['history_class']){
                    array_push($elective_arr,$item['history_class']);
                }
                if($item['geography_class']){
                    array_push($elective_arr,$item['geography_class']);
                }
                if($item['other_class']){
                    array_push($elective_arr,$item['other_class']);
                }
                $item['select_exam_text'] = $select_exam;
                $item['elective_text'] = implode(',',$elective_arr);
                $item['school_text'] = db('school')->where('id', $item['school_id'])->value('name');
                $item['tag_text'] = $item['tag']?$this->tag[$item['tag']]:'';
                $item['createtime_text'] = date('Y-m-d H:i:s', $item['create_time']);
                return $item;
            });
        $this->success('班级列表', $list);
    }

    //学生详情
    public function studentDetail()
    {
        if (!$this->request->isGet()) $this->error('请求方式错误！');
        $id = $this->request->get('id');
        if (!$id) $this->error('未知参数！');
        $data = db('student')->where(['id' => $id])->find();
        if (!$data) $this->error('未找到相关信息！');
        $elective_arr = db('school_class')->where(['type' => 'JX'])->where('id', 'in', $data['elective_ids'])->column('name');
        $data['elective_text'] = implode(',', $elective_arr);
        $data['school_text'] = db('school')->where('id', $data['school_id'])->value('name');
        $data['tag_text'] = $data['tag']?$this->tag[$data['tag']]:'';
        $this->success('学生详情', $data);
    }

    //添加学生
    public function addStudent()
    {
        if (!$this->request->isPost()) $this->error('请求方式错误！');
        $name = $this->request->post('name');
        $stu_number = $this->request->post('stu_number');
        $exam_number = $this->request->post('exam_number');
        $entry_year = $this->request->post('entry_year');
        $stage_id = $this->request->post('stage_id');
        $grade_id = $this->request->post('grade_id');
        $class_id = $this->request->post('class_id');
        $tag = $this->request->post('tag');
        $elective_ids = $this->request->post('elective_ids');
        $select_exam_id = $this->request->post('select_exam_id');
        $foreign_subject_ids = $this->request->post('foreign_subject_ids');
        if (!$name || !$stu_number || !$entry_year || !$stage_id || !$grade_id || !$class_id || !in_array($tag, [1, 2, 3, 4, 5])) $this->error('参数错误！');
        $chinese_class = $this->request->post('chinese_class');
        $maths_class = $this->request->post('maths_class');
        $english_class = $this->request->post('english_class');
        $physics_class = $this->request->post('physics_class');
        $chemistry_class = $this->request->post('chemistry_class');
        $organism_class = $this->request->post('organism_class');
        $politics_class = $this->request->post('politics_class');
        $history_class = $this->request->post('history_class');
        $geography_class = $this->request->post('geography_class');
        $other_class = $this->request->post('other_class');
        $parent_name = $this->request->post('parent_name');
        $parent_phone = $this->request->post('parent_phone');

        $school_id = $this->school_id ?? 2;
        $stage_data = db('school_stage')->where('id', $stage_id)->value('name');
        $grade_name = db('school_grade')->where('id', $grade_id)->value('name');
        $class_name = db('school_class')->where('id', $class_id)->value('name');
        $salt      = Random::alnum();
        $password = $this->auth->getEncryptPassword('123456', $salt);
        $info = db('student')->where('stu_number',$stu_number)->find();
        if($info) $this->error('该学号已使用，请确认输入是否正确！');
        $in = db('student')->insertGetId([
            'name' => $name,
            'salt' => $salt,
            'password' => $password,
            'stu_number' => $stu_number,
            'exam_number' => $exam_number,
            'entry_year' => $entry_year,
            'stage_id' => $stage_id,
            'grade_id' => $grade_id,
            'class_id' => $class_id,
            'tag' => $tag,
            'elective_ids' => $elective_ids,
            'select_exam_id' => $select_exam_id,
            'foreign_subject_ids' => $foreign_subject_ids,
            'school_id' => $school_id,
            'stage_data' => $stage_data,
            'grade_name' => $grade_name,
            'class_name' => $class_name,
            'create_time' => time(),

            'chinese_class' => $chinese_class,
            'maths_class' => $maths_class,
            'english_class' => $english_class,
            'physics_class' => $physics_class,
            'chemistry_class' => $chemistry_class,
            'organism_class' => $organism_class,
            'politics_class' => $politics_class,
            'history_class' => $history_class,
            'geography_class' => $geography_class,
            'other_class' => $other_class,
            'parent_name' => $parent_name,
            'parent_phone' => $parent_phone,
        ]);
        if ($in) {
            if ($chinese_class) {
                //语文教学班
                //获取学科id
                $subject_id = db('subject')->where('name','语文')->value('id');
                $this->checkClass($grade_id,$chinese_class,$subject_id);
            }
    
            if ($maths_class) {
                //数学教学班
                //获取学科id
                $subject_id = db('subject')->where('name','数学')->value('id');
                $this->checkClass($grade_id,$maths_class,$subject_id);
            }
    
            if ($english_class) {
                //英语教学班
                //获取学科id
                $subject_id = db('subject')->where('name','英语')->value('id');
                $this->checkClass($grade_id,$english_class,$subject_id);
            }
    
            if ($physics_class) {
                //物理教学班
                //获取学科id
                $subject_id = db('subject')->where('name','物理')->value('id');
                $this->checkClass($grade_id,$physics_class,$subject_id);
            }
    
            if ($chemistry_class) {
                //化学教学班
                //获取学科id
                $subject_id = db('subject')->where('name','化学')->value('id');
                $this->checkClass($grade_id,$chemistry_class,$subject_id);
            }
    
            if ($organism_class) {
                //生物教学班
                //获取学科id
                $subject_id = db('subject')->where('name','生物')->value('id');
                $this->checkClass($grade_id,$organism_class,$subject_id);
            }
    
            if ($politics_class) {
                //政治教学班
                //获取学科id
                $subject_id = db('subject')->where('name','政治')->value('id');
                $this->checkClass($grade_id,$politics_class,$subject_id);
            }
    
            if ($history_class) {
                //历史教学班
                //获取学科id
                $subject_id = db('subject')->where('name','历史')->value('id');
                $this->checkClass($grade_id,$history_class,$subject_id);
            }
    
            if ($geography_class) {
                //地理教学班
                //获取学科id
                $subject_id = db('subject')->where('name','地理')->value('id');
                $this->checkClass($grade_id,$geography_class,$subject_id);
            }
    
            if ($other_class) {
                //其他教学班
                $this->checkClass($grade_id,$other_class);
            }
            $this->success('添加完成');
        } else {
            $this->error('添加失败');
        }
    }
    //检测添加教学班
    protected function checkClass($grade_id,$className,$subject_id='')
    {
        $check = db('school_class')->where(['grade_id'=>$grade_id,'name'=>$className,'type'=>'JX'])->find();
        if ($check) {
            return;
        }else{
            db('school_class')->insertGetId([
                'school_id' => $this->school_id,
                'name' => $className,
                'type' => 'JX',
                'grade_id' => $grade_id,
                'subject_id' => $subject_id,
                'createtime' => time()
            ]);
            return;
        }
    }

    //编辑学生
    public function editStudent()
    {
        if (!$this->request->isPost()) $this->error('请求方式错误！');
        $id = $this->request->post('id');
        $name = $this->request->post('name');
        $stu_number = $this->request->post('stu_number');
        $exam_number = $this->request->post('exam_number');
        $entry_year = $this->request->post('entry_year');
        $stage_id = $this->request->post('stage_id');
        $grade_id = $this->request->post('grade_id');
        $class_id = $this->request->post('class_id');
        $tag = $this->request->post('tag');
        $elective_ids = $this->request->post('elective_ids');
        $select_exam_id = $this->request->post('select_exam_id');
        $foreign_subject_ids = $this->request->post('foreign_subject_ids');

        $chinese_class = $this->request->post('chinese_class');
        $maths_class = $this->request->post('maths_class');
        $english_class = $this->request->post('english_class');
        $physics_class = $this->request->post('physics_class');
        $chemistry_class = $this->request->post('chemistry_class');
        $organism_class = $this->request->post('organism_class');
        $politics_class = $this->request->post('politics_class');
        $history_class = $this->request->post('history_class');
        $geography_class = $this->request->post('geography_class');
        $other_class = $this->request->post('other_class');
        $parent_name = $this->request->post('parent_name');
        $parent_phone = $this->request->post('parent_phone');
        

        $model = new Student();
        $row = $model->find($id);
        if (empty($row)) $this->error("参数错误！");
        if ($name) {
            $row->name = $name;
        }
        if ($stu_number) {
            $row->stu_number = $stu_number;
        }
        if ($exam_number) {
            $row->exam_number = $exam_number;
        }
        if ($entry_year) {
            $row->entry_year = $entry_year;
        }
        if ($stage_id) {
            $stage_data = db('school_stage')->where('id', $stage_id)->value('name');
            $row->stage_id = $stage_id;
            $row->stage_data = $stage_data;
        }
        if ($grade_id) {
            $grade_name = db('school_grade')->where('id', $grade_id)->value('name');
            $row->grade_id = $grade_id;
            $row->grade_name = $grade_name;
        }
        if ($class_id) {
            $class_name = db('school_class')->where('id', $class_id)->value('name');
            $row->class_id = $class_id;
            $row->class_name = $class_name;
        }
        if ($tag) {
            $row->tag = $tag;
        }
        if ($elective_ids) {
            $row->elective_ids = $elective_ids;
        }
        if ($entry_year) {
            $row->entry_year = $entry_year;
        }
        if ($select_exam_id) {
            $row->select_exam_id = $select_exam_id;
        }
        if ($foreign_subject_ids) {
            $row->foreign_subject_ids = $foreign_subject_ids;
        }
        if ($parent_name) {
            $row->parent_name = $parent_name;
        }
        if ($parent_phone) {
            $row->parent_phone = $parent_phone;
        }

        if ($chinese_class) {
            $row->chinese_class = $chinese_class;
            //语文教学班
            //获取学科id
            $subject_id = db('subject')->where('name','语文')->value('id');
            $this->checkClass($grade_id,$chinese_class,$subject_id);
        }

        if ($maths_class) {
            $row->maths_class = $maths_class;
            //数学教学班
            //获取学科id
            $subject_id = db('subject')->where('name','数学')->value('id');
            $this->checkClass($grade_id,$maths_class,$subject_id);
        }

        if ($english_class) {
            $row->english_class = $english_class;
            //英语教学班
            //获取学科id
            $subject_id = db('subject')->where('name','英语')->value('id');
            $this->checkClass($grade_id,$english_class,$subject_id);
        }

        if ($physics_class) {
            $row->physics_class = $physics_class;
            //物理教学班
            //获取学科id
            $subject_id = db('subject')->where('name','物理')->value('id');
            $this->checkClass($grade_id,$physics_class,$subject_id);
        }

        if ($chemistry_class) {
            $row->chemistry_class = $chemistry_class;
            //化学教学班
            //获取学科id
            $subject_id = db('subject')->where('name','化学')->value('id');
            $this->checkClass($grade_id,$chemistry_class,$subject_id);
        }

        if ($organism_class) {
            $row->organism_class = $organism_class;
            //生物教学班
            //获取学科id
            $subject_id = db('subject')->where('name','生物')->value('id');
            $this->checkClass($grade_id,$organism_class,$subject_id);
        }

        if ($politics_class) {
            $row->politics_class = $politics_class;
            //政治教学班
            //获取学科id
            $subject_id = db('subject')->where('name','政治')->value('id');
            $this->checkClass($grade_id,$politics_class,$subject_id);
        }

        if ($history_class) {
            $row->history_class = $history_class;
            //历史教学班
            //获取学科id
            $subject_id = db('subject')->where('name','历史')->value('id');
            $this->checkClass($grade_id,$history_class,$subject_id);
        }

        if ($geography_class) {
            $row->geography_class = $geography_class;
            //地理教学班
            //获取学科id
            $subject_id = db('subject')->where('name','地理')->value('id');
            $this->checkClass($grade_id,$geography_class,$subject_id);
        }

        if ($other_class) {
            $row->other_class = $other_class;
            //其他教学班
            $this->checkClass($grade_id,$other_class);
        }


        $row->update_time = time();
        $row->save();
        $this->success('操作完成！');
    }

    //删除学生
    public function delStudent()
    {
        if (!$this->request->isGet()) $this->error('请求方式错误！');
        $ids = $this->request->get('ids');
        if (!$ids) $this->error('未知参数！');
        db('student')->where('id', 'in', $ids)->delete();
        $this->success('操作成功');
    }

    

    //导出
    public function exportExcel()
    {
        if (!$this->request->isPost()) $this->error('请求方式错误！');
        $school_id = $this->school_id ?? 2;
        $name = $this->request->post('name');
        $parent_phone = $this->request->post('parent_phone');
        $exam_number = $this->request->post('exam_number');
        $grade_id = $this->request->post('grade_id');
        $class_id = $this->request->post('class_id');
        $tag = $this->request->post('tag');
        $select_exam_id = $this->request->post('select_exam_id');
        $data = db('student')
            ->where(function ($query) use ($name) {
                if ($name) {
                    $query->where('name', 'like', '%' . $name . '%');
                }
            })
            ->where(function ($query) use ($grade_id) {
                if ($grade_id) {
                    $query->where('grade_id', $grade_id);
                }
            })
            ->where(function ($query) use ($parent_phone) {
                if ($parent_phone) {
                    $query->where('parent_phone', $parent_phone);
                }
            })
            ->where(function ($query) use ($exam_number) {
                if ($exam_number) {
                    $query->where('exam_number', $exam_number);
                }
            })
            ->where(function ($query) use ($class_id) {
                if ($class_id) {
                    $query->where('class_id', $class_id);
                }
            })
            ->where(function ($query) use ($tag) {
                if ($tag) {
                    $query->where('tag', $tag);
                }
            })
            ->where(function ($query) use ($select_exam_id) {
                if ($select_exam_id) {
                    $query->where('select_exam_id', $select_exam_id);
                }
            })
            ->where(['status' => 'normal', 'school_id' => $school_id])->order('create_time desc')->select();

        // 处理数据
        foreach ($data as &$v) {
            $elective_arr = db('school_class')->where(['type' => 'JX'])->where('id', 'in', $v['elective_ids'])->column('name');
            $v['elective_text'] = implode(',', $elective_arr);
            $v['school_text'] = db('school')->where('id', $v['school_id'])->value('name');
            $v['tag_text'] = $v['tag']?$this->tag[$v['tag']]:'';

            $select_exam = db('select_exam')->where('id', $v['select_exam_id'])->value('name');
            $forSubject = db('subject')->where('id', $v['foreign_subject_ids'])->value('name');
            $v['tag'] = $v['tag']?$this->tag[$v['tag']]:'';
            $v['select_exam_name'] = $select_exam;
            $v['foreign_subject'] =  $forSubject;
            //            $v['created_at'] = date('Y/m/d H:i:s', $v['created_at']);
        }
        unset($v);
        // 加载sheet
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Sheet1');

        // 处理标题
        $title = [
            'id' => '序号',
            'school_text' => '学校',
            'name' => '姓名',
            'tag' => '学生标签',
            'stu_number' => '学号',
            'exam_number' => '考号',
            'entry_year' => '入学年',
            'stage_data' => '学段',
            'grade_name' => '年级',
            'class_name' => '行政班',
            'chinese_class' => '语文教学班',
            'maths_class' => '数学教学班',
            'english_class' => '英语教学班',
            'physics_class' => '物理教学班',
            'chemistry_class' => '化学教学班',
            'organism_class' => '生物教学班',
            'politics_class' => '政治教学班',
            'history_class' => '历史教学班',
            'geography_class' => '地理教学班',
            'other_class' => '其他教学班',
            'select_exam_name' => '选考科目',
            'foreign_subject' => '外语科目',
            'select_exam_name' => '选考科目',
            'foreign_subject' => '外语科目',
            'parent_name' => '家长姓名',
            'parent_phone' => '家长电话'
        ];
        $keys = 1;
        foreach ($title as $key => $value) {
            $sheet->setCellValueByColumnAndRow($keys, 1, $value);
            $sheet->getDefaultColumnDimension()->setWidth(15);
            $keys++;
        }

        // 处理标题下内容
        // 从第二行开始
        $row = 2;
        foreach ($data as $item) {
            $column = 1;
            foreach ($title as $key => $value) {
                $sheet->setCellValueByColumnAndRow($column, $row, $item[$key]);
                $column++;
            }
            $row++;
        }

        // 文件下载
        $filename = './uploads/学员信息' . time() . '.xlsx';

        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
    }

    //学生导入
    public function importStudent()
    {
        $model = new Student();
        $file = $this->request->post('file');
        if (!$file) {
            $this->error(__('Parameter %s can not be empty', 'file'));
        }
        $filePath = ROOT_PATH . DS . 'public' . DS . $file;
        if (!is_file($filePath)) {
            $this->error(__('No results were found'));
        }

        //实例化reader
        $ext = pathinfo($filePath, PATHINFO_EXTENSION);
        if (!in_array($ext, ['csv', 'xls', 'xlsx'])) {
            $this->error(__('Unknown data format'));
        }
        if ($ext === 'csv') {
            $file = fopen($filePath, 'r');
            $filePath = tempnam(sys_get_temp_dir(), 'import_csv');
            $fp = fopen($filePath, 'w');
            $n = 0;
            while ($line = fgets($file)) {
                $line = rtrim($line, "\n\r\0");
                $encoding = mb_detect_encoding($line, ['utf-8', 'gbk', 'latin1', 'big5']);
                if ($encoding !== 'utf-8') {
                    $line = mb_convert_encoding($line, 'utf-8', $encoding);
                }
                if ($n == 0 || preg_match('/^".*"$/', $line)) {
                    fwrite($fp, $line . "\n");
                } else {
                    fwrite($fp, '"' . str_replace(['"', ','], ['""', '","'], $line) . "\"\n");
                }
                $n++;
            }
            fclose($file) || fclose($fp);

            $reader = new Csv();
        } elseif ($ext === 'xls') {
            $reader = new Xls();
        } else {
            $reader = new Xlsx();
        }

        //导入文件首行类型,默认是注释,如果需要使用字段名称请使用name
        $importHeadType = isset($this->importHeadType) ? $this->importHeadType : 'comment';

        $table = $model->getQuery()->getTable();
        $database = \think\Config::get('database.database');
        $fieldArr = [];
        $list = db()->query("SELECT COLUMN_NAME,COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ? AND TABLE_SCHEMA = ?", [$table, $database]);
        foreach ($list as $k => $v) {
            if ($importHeadType == 'comment') {
                $v['COLUMN_COMMENT'] = explode(':', $v['COLUMN_COMMENT'])[0]; //字段备注有:时截取
                $fieldArr[$v['COLUMN_COMMENT']] = $v['COLUMN_NAME'];
            } else {
                $fieldArr[$v['COLUMN_NAME']] = $v['COLUMN_NAME'];
            }
        }

        //加载文件
        $insert = [];

        try {
            $tags = ['复读生', '借读生', '艺术生', '体育生', '学生'];
            if (!$PHPExcel = $reader->load($filePath)) {
                $this->error(__('Unknown data format'));
            }
            $currentSheet = $PHPExcel->getSheet(0);  //读取文件中的第一个工作表
            $allColumn = $currentSheet->getHighestDataColumn(); //取得最大的列号
            $allRow = $currentSheet->getHighestRow(); //取得一共有多少行
            $maxColumnNumber = Coordinate::columnIndexFromString($allColumn);
            $fields = [];
            for ($currentRow = 1; $currentRow <= 1; $currentRow++) {
                for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                    $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                    $fields[] = $val;
                }
            }
            $school_id = $this->school_id;
            for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
                $values = [];
                for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                    $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                    $values[] = is_null($val) ? '' : $val;
                }
                $row = [];
                $temp = array_combine($fields, $values);
                
                foreach ($temp as $k => $v) {
                    if (isset($fieldArr[$k]) && $k !== '') {
                        
                        $row['school_id'] = $school_id;
                        $row['salt']      = Random::alnum();
                        $row['password'] = $this->auth->getEncryptPassword('123456', $row['salt']);
                        $row['create_time']  = time();
                        $row[$fieldArr[$k]] = $v;

                        if ($fieldArr[$k] == 'grade_id') {
                            $row[$fieldArr[$k]] = db('school_grade')->where(['school_id' => $school_id, 'name' => $v])->value('id')??0;
                            $row['grade_name'] = $v;
                            $row['stage_id'] = db('school_grade')->where(['school_id' => $school_id, 'name' => $v])->value('stage_id')??0;
                            $row['stage_data'] = db('school_stage')->where(['id' => $row['stage_id']])->value('name')??'';
                        }
                        if ($fieldArr[$k] == 'class_id') {
                            $row[$fieldArr[$k]] =  db('school_class')->where(['school_id' => $school_id,'type'=>'XZ','name' => $v])->value('id');
                            $row['class_name'] = $v;
                        }
                        if ($fieldArr[$k] == 'tag') {
                            $row[$fieldArr[$k]] = in_array($v, $tags) ? array_search($v, $tags) + 1 : '';
                        }
                        if ($fieldArr[$k] == 'elective_ids') {
                            $row[$fieldArr[$k]] = implode(',', db('school_class')->whereIn('name', $v)->where(['school_id' => $row['school_id']])->column('id'));
                        }
                        if ($fieldArr[$k] == 'foreign_subject_ids') {
                            $row[$fieldArr[$k]] = implode(',', db('subject')->whereIn('name', $v)->where(['school_id' => $row['school_id'], 'type' => 2])->column('id'));
                        }

                        if ($fieldArr[$k] == 'select_exam_id') {
                            $row[$fieldArr[$k]] =  db('select_exam')->where(['school_id' => $school_id, 'name' => $v])->value('id');
                        }
                        
                    }
                }

                if ($row) {
                    unset($row['id']);
                    $insert[] = $row;
                }
            }
        } catch (Exception $exception) {
            $this->error($exception->getMessage());
        }
        if (!$insert) {
            $this->error(__('No rows were updated'));
        }
        $errorMessage = [];
        try {
            foreach ($insert as $key => $value) {
                $info = db('student')->where(['school_id'=>$school_id,'stu_number'=>$value['stu_number']])->find();
                if($info){
                    array_push($errorMessage,$value['stu_number'].'该学号已存在，请确认输入是否正确！');
                    continue;
                }else{
                    $in = db('student')->insert($value);
                    if ($in) {
                        if ($value['chinese_class']) {
                            //语文教学班
                            //获取学科id
                            $subject_id = db('subject')->where('name','语文')->value('id');
                            $this->checkClass($value['grade_id'],$value['chinese_class'],$subject_id);
                        }
                
                        if ($value['maths_class']) {
                            //数学教学班
                            //获取学科id
                            $subject_id = db('subject')->where('name','数学')->value('id');
                            $this->checkClass($value['grade_id'],$value['maths_class'],$subject_id);
                        }
                
                        if ($value['english_class']) {
                            //英语教学班
                            //获取学科id
                            $subject_id = db('subject')->where('name','英语')->value('id');
                            $this->checkClass($value['grade_id'],$value['english_class'],$subject_id);
                        }
                
                        if ($value['physics_class']) {
                            //物理教学班
                            //获取学科id
                            $subject_id = db('subject')->where('name','物理')->value('id');
                            $this->checkClass($value['grade_id'],$value['physics_class'],$subject_id);
                        }
                
                        if ($value['chemistry_class']) {
                            //化学教学班
                            //获取学科id
                            $subject_id = db('subject')->where('name','化学')->value('id');
                            $this->checkClass($value['grade_id'],$value['chemistry_class'],$subject_id);
                        }
                
                        if ($value['organism_class']) {
                            //生物教学班
                            //获取学科id
                            $subject_id = db('subject')->where('name','生物')->value('id');
                            $this->checkClass($value['grade_id'],$value['organism_class'],$subject_id);
                        }
                
                        if ($value['politics_class']) {
                            //政治教学班
                            //获取学科id
                            $subject_id = db('subject')->where('name','政治')->value('id');
                            $this->checkClass($value['grade_id'],$value['politics_class'],$subject_id);
                        }
                
                        if ($value['history_class']) {
                            //历史教学班
                            //获取学科id
                            $subject_id = db('subject')->where('name','历史')->value('id');
                            $this->checkClass($value['grade_id'],$value['history_class'],$subject_id);
                        }
                
                        if ($value['geography_class']) {
                            //地理教学班
                            //获取学科id
                            $subject_id = db('subject')->where('name','地理')->value('id');
                            $this->checkClass($value['grade_id'],$value['geography_class'],$subject_id);
                        }
                
                        if ($value['other_class']) {
                            //其他教学班
                            $this->checkClass($value['grade_id'],$value['other_class']);
                        }
                    }
                }
                
            }
            if ($errorMessage) {
                throw new Exception(implode(',',$errorMessage));
                
            }
            // $model->saveAll($insert);
        } catch (PDOException $exception) {
            $msg = $exception->getMessage();
            if (preg_match("/.+Integrity constraint violation: 1062 Duplicate entry '(.+)' for key '(.+)'/is", $msg, $matches)) {
                $msg = "导入失败，包含【{$matches[1]}】的记录已存在";
            };
            $this->error('错误信息！',$msg,300);
        } catch (Exception $e) {
            $this->error('错误信息！',$e->getMessage(),300);
        }

        $this->success();
    }


    //添加年级组
    public function addSchoolStage()
    {
        #school_stage
        if (!$this->request->isPost()) $this->error('请求方式错误！');
        $name = $this->request->post('name');
        $grade = $this->request->param('grade/a');
        if (!$name) $this->error('名称错误');
        if (!is_array($grade)) $this->error('参数格式错误');
        $result = false;
        Db::startTrans();
        try {
            $stageId = db('school_stage')->insertGetId([
                'school_id' => $this->school_id ?? 1,
                'name' => $name,
                'createtime' => time()
            ]);
            if (!$stageId) throw new Exception("添加失败！");
            $gData = [];
            foreach ($grade as $key => $value) {
                $gData[$key]['school_id'] = $this->school_id;
                $gData[$key]['stage_id'] = $stageId;
                $gData[$key]['common_grade_id'] = $value['id'];
                $gData[$key]['name'] = $value['name'];
                $gData[$key]['number'] = $value['number'];
                $gData[$key]['createtime'] = time();
            }
            db('school_grade')->insertAll($gData);
            $result = true;
            Db::commit();
        } catch (Exception $e) {
            Db::rollback();
            $this->error($e->getMessage());
        }
        if ($result) {
            $this->success('添加成功！');
        } else {
            $this->error('添加失败！');
        }
    }

    

    //年级组列表
    public function schoolStageList()
    {
        if (!$this->request->isGet()) $this->error('请求方式错误！');
        $school_id = $this->school_id;
        $list = db('school_stage')->where(['status' => 1, 'school_id' => $school_id])->field('id,name')->select();
        if (!$list) $this->error('未找到相关信息！');
        foreach ($list as &$value) {
            $value['grade'] = db('school_grade')->where(['stage_id' => $value['id']])->field('common_grade_id,name,number')->order('number asc')->select();
        }
        $this->success('年级组列表', $list);
    }

    //年级组详情
    public function schoolStageDetail()
    {
        if (!$this->request->isGet()) $this->error('请求方式错误！');
        $id = $this->request->get('id');
        if (!$id) $this->error('未知参数！');
        $data = db('school_stage')->where(['id' => $id])->field('id,name')->find();
        if (!$data) $this->error('未找到相关信息！');
        $data['grade'] = db('school_grade')->where(['stage_id' => $data['id']])->field('common_grade_id,name,number')->order('number asc')->select();
        $this->success('年级组详情', $data);
    }

    //编辑年级组
    public function editSchoolStage()
    {
        #school_stage
        if (!$this->request->isPost()) $this->error('请求方式错误！');
        $id = $this->request->post('id');
        $name = $this->request->post('name');
        $grade = $this->request->param('grade/a');
        if (!$id || !$name) $this->error('参数错误！');
        if (!is_array($grade)) $this->error('参数格式错误！');
        $info = db('school_stage')->where(['id' => $id])->find();
        if (!$info) $this->error('未找到相关信息！');
        $result = false;
        Db::startTrans();
        try {
            db('school_stage')->where(['id' => $id])->update(['name' => $name]);
            db('school_grade')->where(['stage_id' => $id])->delete();
            $gData = [];
            foreach ($grade as $key => $value) {
                $gData[$key]['school_id'] = $info['school_id'];
                $gData[$key]['stage_id'] = $id;
                $gData[$key]['common_grade_id'] = $value['id'];
                $gData[$key]['name'] = $value['name'];
                $gData[$key]['number'] = $value['number'];
                $gData[$key]['createtime'] = time();
            }
            db('school_grade')->insertAll($gData);
            $result = true;
            Db::commit();
        } catch (Exception $e) {
            Db::rollback();
            $this->error($e->getMessage());
        }
        if ($result) {
            $this->success('操作成功！');
        } else {
            $this->error('操作失败！');
        }
    }

    //删除年级组
    public function delSchoolStage()
    {
        if (!$this->request->isGet()) $this->error('请求方式错误！');
        $id = $this->request->get('id');
        if (!$id) $this->error('未知参数！');
        db('school_stage')->where(['id' => $id])->delete();
        db('school_grade')->where(['stage_id' => $id])->delete();
        $this->success('操作成功');
    }


    //添加班级
    public function addSchoolClass()
    {
        #school_stage
        if (!$this->request->isPost()) $this->error('请求方式错误！');
        $type = $this->request->post('type');
        $name = $this->request->post('name');
        $grade_id = $this->request->post('grade_id');
        $subject_id = $this->request->post('subject_id');
        if (!in_array($type, ['XZ', 'JX']) || !$name || !$grade_id) $this->error('参数错误');
        if ($type == 'JX') {
            if (!$subject_id) $this->error('请选择科目');
        }
        $classId = db('school_class')->insertGetId([
            'school_id' => $this->school_id,
            'name' => $name,
            'type' => $type,
            'grade_id' => $grade_id,
            'subject_id' => $subject_id,
            'createtime' => time()
        ]);
        if ($classId) {
            $this->success('添加成功！');
        } else {
            $this->error('添加失败！');
        }
    }



    //班级列表
    public function schoolClassList()
    {
        if (!$this->request->isGet()) $this->error('请求方式错误！');
        $school_id = $this->school_id;
        $grade_id = $this->request->get('grade_id');
        $type = $this->request->get('type');
        $page = $this->request->get('page');
        $pagesize = $this->request->get('pagesize');
        if (!$page || !$pagesize) $this->error('参数错误！');
        $list = db('school_class')
            ->where(function ($query) use ($grade_id) {
                if ($grade_id) {
                    $query->where('grade_id', $grade_id);
                }
            })
            ->where(function ($query) use ($type) {
                if ($type) {
                    $query->where('type', $type);
                }
            })
            ->where(['status' => 'normal', 'school_id' => $school_id])->order('createtime desc')->paginate($pagesize, false)->each(function ($item, $index) {
                $item['school_text'] = db('school')->where('id', $item['school_id'])->value('name');
                $item['grade_text'] = db('school_grade')->where('id', $item['grade_id'])->value('name');
                $item['subject_text'] = db('subject')->where('id', $item['subject_id'])->value('name');
                $item['status_text'] = $this->status[$item['status']];
                $item['createtime_text'] = date('Y-m-d H:i:s', $item['createtime']);
                return $item;
            });
        $this->success('班级列表', $list);
    }

    //班级详情
    public function schoolClassDetail()
    {
        if (!$this->request->isGet()) $this->error('请求方式错误！');
        $id = $this->request->get('id');
        if (!$id) $this->error('未知参数！');
        $data = db('school_class')->where(['id' => $id])->find();
        if (!$data) $this->error('未找到相关信息！');
        $data['school_text'] = db('school')->where('id', $data['school_id'])->value('name');
        $data['grade_text'] = db('school_grade')->where('id', $data['grade_id'])->value('name');
        $data['subject_text'] = db('subject')->where('id', $data['subject_id'])->value('name');
        $data['status_text'] = $this->status[$data['status']];
        $data['createtime_text'] = date('Y-m-d H:i:s', $data['createtime']);
        $this->success('年级组详情', $data);
    }

    //编辑班级
    public function editSchoolClass()
    {
        #school_stage
        if (!$this->request->isPost()) $this->error('请求方式错误！');
        $id = $this->request->post('id');
        $type = $this->request->post('type');
        $name = $this->request->post('name');
        $grade_id = $this->request->post('grade_id');
        $subject_id = $this->request->post('subject_id');
        if (!$id || !in_array($type, ['XZ', 'JX']) || !$name || !$grade_id) $this->error('参数错误');
        if ($type == 'JX') {
            if (!$subject_id) $this->error('请选择科目');
        }
        $classUp = db('school_class')->where('id', $id)->update([
            'name' => $name,
            'type' => $type,
            'grade_id' => $grade_id,
            'subject_id' => $subject_id,
            'createtime' => time()
        ]);
        if ($classUp) {
            $this->success('操作成功！');
        } else {
            $this->error('操作失败！');
        }
    }

    //删除班级
    public function delSchoolClass()
    {
        if (!$this->request->isGet()) $this->error('请求方式错误！');
        $id = $this->request->get('id');
        if (!$id) $this->error('未知参数！');
        db('school_class')->where(['id' => $id])->delete();
        $this->success('操作成功');
    }


    //班级导出
    public function exportClass()
    {
        if (!$this->request->isPost()) $this->error('请求方式错误！');
        $school_id = $this->school_id ?? 2;
        $grade_id = $this->request->post('grade_id');
        $type = $this->request->post('type');//班级类型 XZ=行政班，JX=教学班

        $data = db('school_class')
            ->where(function ($query) use ($grade_id) {
                if ($grade_id) {
                    $query->where('grade_id', $grade_id);
                }
            })
            ->where(function ($query) use ($type) {
                if ($type) {
                    $query->where('type', $type);
                }
            })
            ->where(['status' => 'normal', 'school_id' => $school_id])->order('createtime desc')->select();

        // 处理数据
        foreach ($data as &$v) {
            $v['type_text'] = $this->classType[$v['type']];
            $v['school_text'] = db('school')->where('id', $v['school_id'])->value('name');
            $v['grade_text'] = db('school_grade')->where('id', $v['grade_id'])->value('name');
            $v['status_text'] = $this->status[$v['status']];
            $v['subject_text'] = '';
            if ($v['type'] == 'JX') {
                $v['subject_text'] = db('subject')->where('id', $v['subject_id'])->value('name');
            }
        }
        unset($v);
        // 加载sheet
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Sheet1');

        // 处理标题
        $title = [
            'id' => '序号',
            'name' => '班级名称',
            // 'school_text' => '学校',
            'grade_text' => '所在年级',
            'type_text' => '班级类型',
            'subject_text' => '教学班科目'
        ];
        $keys = 1;
        foreach ($title as $key => $value) {
            $sheet->setCellValueByColumnAndRow($keys, 1, $value);
            $sheet->getDefaultColumnDimension()->setWidth(15);
            $keys++;
        }

        // 处理标题下内容
        // 从第二行开始
        $row = 2;
        foreach ($data as $item) {
            $column = 1;
            foreach ($title as $key => $value) {
                $sheet->setCellValueByColumnAndRow($column, $row, $item[$key]);
                $column++;
            }
            $row++;
        }

        // 文件下载
        $filename = './uploads/班级信息' . time() . '.xlsx';

        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
    }

    //班级导入
    public function importClass()
    {
        $model = new Gclass();
        $file = $this->request->post('file');
        if (!$file) {
            $this->error(__('Parameter %s can not be empty', 'file'));
        }
        $filePath = ROOT_PATH . DS . 'public' . DS . $file;
        if (!is_file($filePath)) {
            $this->error(__('No results were found'));
        }

        //实例化reader
        $ext = pathinfo($filePath, PATHINFO_EXTENSION);
        if (!in_array($ext, ['csv', 'xls', 'xlsx'])) {
            $this->error(__('Unknown data format'));
        }
        if ($ext === 'csv') {
            $file = fopen($filePath, 'r');
            $filePath = tempnam(sys_get_temp_dir(), 'import_csv');
            $fp = fopen($filePath, 'w');
            $n = 0;
            while ($line = fgets($file)) {
                $line = rtrim($line, "\n\r\0");
                $encoding = mb_detect_encoding($line, ['utf-8', 'gbk', 'latin1', 'big5']);
                if ($encoding !== 'utf-8') {
                    $line = mb_convert_encoding($line, 'utf-8', $encoding);
                }
                if ($n == 0 || preg_match('/^".*"$/', $line)) {
                    fwrite($fp, $line . "\n");
                } else {
                    fwrite($fp, '"' . str_replace(['"', ','], ['""', '","'], $line) . "\"\n");
                }
                $n++;
            }
            fclose($file) || fclose($fp);

            $reader = new Csv();
        } elseif ($ext === 'xls') {
            $reader = new Xls();
        } else {
            $reader = new Xlsx();
        }

        //导入文件首行类型,默认是注释,如果需要使用字段名称请使用name
        $importHeadType = isset($this->importHeadType) ? $this->importHeadType : 'comment';

        $table = $model->getQuery()->getTable();
        $database = \think\Config::get('database.database');
        $fieldArr = [];
        $list = db()->query("SELECT COLUMN_NAME,COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ? AND TABLE_SCHEMA = ?", [$table, $database]);
        foreach ($list as $k => $v) {
            if ($importHeadType == 'comment') {
                $v['COLUMN_COMMENT'] = explode(':', $v['COLUMN_COMMENT'])[0]; //字段备注有:时截取
                $fieldArr[$v['COLUMN_COMMENT']] = $v['COLUMN_NAME'];
            } else {
                $fieldArr[$v['COLUMN_NAME']] = $v['COLUMN_NAME'];
            }
        }

        //加载文件
        $insert = [];

        try {
           
            if (!$PHPExcel = $reader->load($filePath)) {
                $this->error(__('Unknown data format'));
            }
            $currentSheet = $PHPExcel->getSheet(0);  //读取文件中的第一个工作表
            $allColumn = $currentSheet->getHighestDataColumn(); //取得最大的列号
            $allRow = $currentSheet->getHighestRow(); //取得一共有多少行
            $maxColumnNumber = Coordinate::columnIndexFromString($allColumn);
            $fields = [];
            for ($currentRow = 1; $currentRow <= 1; $currentRow++) {
                for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                    $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                    $fields[] = $val;
                }
            }
            $school_id = $this->school_id;
            for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
                $values = [];
                for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                    $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                    $values[] = is_null($val) ? '' : $val;
                }
                $row = [];
                $temp = array_combine($fields, $values);
                foreach ($temp as $k => $v) {
                    
                    if (isset($fieldArr[$k]) && $k !== '') {
                        
                        $row['school_id'] = $school_id;
                        $row['status'] = 'normal';
                        $row['createtime'] = time();
                        $row[$fieldArr[$k]] = $v;
                        
                        if ($fieldArr[$k] == 'grade_id') {
                            
                            $row[$fieldArr[$k]] = db('school_grade')->where(['school_id' => $school_id, 'name' => $v])->value('id')??0;
                            
                        }
                        if ($fieldArr[$k] == 'type') {
                            
                            if($v == '教学班'){
                                $kk = 'JX';
                            }
                            if($v == '行政班'){
                                $kk = 'XZ';
                            }
                            $row[$fieldArr[$k]] =  $kk;
                        }
                        if ($fieldArr[$k] == 'subject_id') {
                            $row[$fieldArr[$k]] = db('subject')->where(['name' => $v])->value('id')??0;
                        }
                         
                    }
                }

                if ($row) {
                    unset($row['id']);
                    $insert[] = $row;
                }
            }
        } catch (Exception $exception) {
            $this->error($exception->getMessage());
        }
        if (!$insert) {
            $this->error(__('No rows were updated'));
        }
        $errorMessage = [];
        try {
            foreach ($insert as $key => $value) {
                if ($value['subject_id']) {
                    $info = db('school_class')->where(['school_id'=>$school_id,'grade_id'=>$value['grade_id'],'type'=>$value['type'],'subject_id'=>$value['subject_id'],'name'=>$value['name']])->find();
                }else{
                    $info = db('school_class')->where(['school_id'=>$school_id,'grade_id'=>$value['grade_id'],'type'=>$value['type'],'name'=>$value['name']])->find();
                }
                
                if($info){
                    array_push($errorMessage,$value['name'].'该班级已存在，请确认输入是否正确！');
                    continue;
                }else{
                    
                    db('school_class')->insert($value);
                }
                
            }
            if ($errorMessage) {
                throw new Exception(implode(',',$errorMessage));
                
            }
            // $model->saveAll($insert);
        } catch (PDOException $exception) {
            $msg = $exception->getMessage();
            if (preg_match("/.+Integrity constraint violation: 1062 Duplicate entry '(.+)' for key '(.+)'/is", $msg, $matches)) {
                $msg = "导入失败，包含【{$matches[1]}】的记录已存在";
            };
            $this->error('错误信息！',$msg,300);
        } catch (Exception $e) {
            $this->error('错误信息！',$e->getMessage(),300);
        }

        $this->success();
    }

    //添加教师
    public function addSchoolTeacher()
    {
        if (!$this->request->isPost()) $this->error('请求方式错误！');
        $school_id = $this->school_id ?? 1;
        $mobile = $this->request->post('mobile');
        $name = $this->request->post('name');
        $email = $this->request->post('email');
        $role = $this->request->param('role/a');
        if (!$name || !$mobile ) $this->error('未知参数！');
        if (!is_array($role)) $this->error('参数格式错误！');
        // if ($email && !Validate::is($email, "email")) {
        //     $this->error(__('Email is incorrect'));
        // }
        if ($mobile && !Validate::regex($mobile, "^1\d{10}$")) {
            $this->error(__('Mobile is incorrect'));
        }

        $groupIds = array_column($role, 'id');
        $groupIds = implode(',', $groupIds);
        $ret = $this->auth->registers($mobile, $mobile, $email, $mobile, $name, ['group_ids' => $groupIds, 'school_id' => $school_id], $role);
        if ($ret) {
            $this->success('添加成功！');
        } else {
            $this->error('添加失败！');
        }
    }

    //编辑教师
    public function editSchoolTeacher()
    {
        if (!$this->request->isPost()) $this->error('请求方式错误！');
        $id = $this->request->post('id');
        $mobile = $this->request->post('mobile');
        $name = $this->request->post('name');
        $email = $this->request->post('email');
        $role = $this->request->param('role/a');
        if (!$name || !$mobile) $this->error('未知参数！');
        if (!is_array($role)) $this->error('参数格式错误！');
        // if ($email && !Validate::is($email, "email")) {
        //     $this->error(__('Email is incorrect'));
        // }
        if ($mobile && !Validate::regex($mobile, "^1\d{10}$")) {
            $this->error(__('Mobile is incorrect'));
        }
        $user = db('user')->where('id', $id)->find();
        if (!$user) $this->error('未找到相关信息！');
        $groupIds = array_column($role, 'id');
        $groupIds = implode(',', $groupIds);
        $result = false;
        Db::startTrans();
        try {
            $up = db('user')->where(['id' => $id])->update([
                'username' => $mobile,
                'username' => $mobile,
                'nickname'  => $name,
                'email'    => $email,
                'mobile'   => $mobile
            ]);

            db('user_role')->where(['user_id' => $id])->delete();
            $rData = [];
            foreach ($role as $key => $value) {
                $rData[$key]['school_id'] = $user['school_id'];
                $rData[$key]['user_id'] = $user['id'];
                $rData[$key]['group_id'] = $value['id'];
                $rData[$key]['grade_id'] = $value['grade_id'];
                $rData[$key]['gclass_ids'] = $value['gclass_ids'];
                $rData[$key]['elective_ids'] = $value['elective_ids'];
                $rData[$key]['subject_id'] = $value['subject_id'];
            }
            db('user_role')->insertAll($rData);
            $result = true;
            Db::commit();
        } catch (Exception $e) {
            Db::rollback();
            $this->error($e->getMessage());
        }
        if ($result) {
            $this->success('操作成功！');
        } else {
            $this->error('操作失败！');
        }
    }

    //删除教师
    public function delSchoolTeacher()
    {
        if (!$this->request->isGet()) $this->error('请求方式错误！');
        $ids = $this->request->get('ids');
        if (!$ids) $this->error('未知参数！');
        db('user')->where('id', 'in', $ids)->delete();
        db('user_role')->where('user_id', 'in', $ids)->delete();
        $this->success('操作成功');
    }


    //教师列表
    public function schoolTeacherList()
    {
        if (!$this->request->isPost()) $this->error('请求方式错误！');
        $school_id = $this->school_id;
        $group_id = $this->request->post('group_id');
        $grade_id = $this->request->post('grade_id');
        $gclass_ids = $this->request->post('gclass_ids');
        $elective_ids = $this->request->post('elective_ids');
        $subject_id = $this->request->post('subject_id');
        $isguan = $this->request->post('isguan');
        $page = $this->request->post('page');
        $pagesize = $this->request->post('pagesize');
        if (!$page || !$pagesize) $this->error('参数错误！');
        $list = db('user')
            ->alias('u')
            ->join('user_role r', ['r.user_id=u.id'], 'LEFT')
            ->where(function ($query) use ($grade_id) {
                if ($grade_id) {
                    $query->where('r.grade_id', $grade_id);
                }
            })
            ->where(function ($query) use ($gclass_ids) {
                if ($gclass_ids) {
                    $query->where('find_in_set(:keywords,r.gclass_ids)', ['keywords' => $gclass_ids]);
                }
            })
            ->where(function ($query) use ($elective_ids) {
                if ($elective_ids) {
                    $query->where('find_in_set(:keywords,r.elective_ids)', ['keywords' => $elective_ids]);
                }
            })
            ->where(function ($query) use ($subject_id) {
                if ($subject_id) {
                    $query->where('r.subject_id', $subject_id);
                }
            })
            ->where(function ($query) use ($isguan) {
                if (in_array($isguan, [0, 1])) {
                    $query->where('u.isguan', $isguan);
                }
            })
            ->where(function ($query) use ($group_id) {
                if ($group_id) {
                    $query->where('find_in_set(:keywords,u.group_ids)', ['keywords' => $group_id]);
                }
            })
            ->where(['u.school_id' => $school_id])
            ->field('u.id,u.nickname as name,u.mobile,u.school_id,u.group_ids,u.isguan,r.id as rid')
            ->Distinct(true)
            ->paginate($pagesize, false)->each(function ($item, $index) {
                $list = db('user_role')->where('user_id', $item['id'])->select();
                foreach ($list as &$value) {
                    $value['grade_text'] = db('school_grade')->where('id', $value['grade_id'])->value('name');
                    $value['group_text'] = db('user_group')->where('id', $value['group_id'])->value('name');
                    $value['gclass_text'] = db('school_class')->where('id', 'in', $value['gclass_ids'])->column('name');
                    $value['elective_text'] = db('school_class')->where('id', 'in', $value['elective_ids'])->column('name');
                    $value['subject_text'] = db('subject')->where('id', $value['subject_id'])->value('name');
                }
                $item['school_text'] = db('school')->where('id', $item['school_id'])->value('name');
                $item['role'] = $list;
                return $item;
            });
        $this->success('教师列表', $list);
    }

    //教师列表
    public function schoolTeacherLists()
    {
        if (!$this->request->isPost()) $this->error('请求方式错误！');
        $school_id = $this->school_id;
        $group_id = $this->request->post('group_id');
        $grade_id = $this->request->post('grade_id');
        $gclass_ids = $this->request->post('gclass_ids');
        $elective_ids = $this->request->post('elective_ids');
        $subject_id = $this->request->post('subject_id');
        $isguan = $this->request->post('isguan', '');
        $page = $this->request->post('page');
        $pagesize = $this->request->post('pagesize');
        if (!$page || !$pagesize) $this->error('参数错误！');
        $user = new User();
        $list = $user
            ->with([
                'role' => function ($query) use ($grade_id, $gclass_ids, $elective_ids, $subject_id) {
                    if ($grade_id) {
                        $query->where('grade_id', $grade_id);
                    }
                    if ($gclass_ids) {
                        $query->where('find_in_set(:keywords,gclass_ids)', ['keywords' => $gclass_ids]);
                    }
                    if ($elective_ids) {
                        $query->where('find_in_set(:keywords,elective_ids)', ['keywords' => $elective_ids]);
                    }
                    if ($subject_id) {
                        $query->where('subject_id', $subject_id);
                    }
                }
            ])
            ->where(function ($query) use ($isguan) {
                if ($isguan && in_array($isguan, [0, 1])) {
                    $query->where('isguan', $isguan);
                }
            })
            ->where(function ($query) use ($group_id) {
                if ($group_id) {
                    $query->where("find_in_set({$group_id}, group_ids)");
                }
            })
            ->where(['school_id' => $school_id, 'type' => "teacher"])
            ->field('id,nickname as name,mobile,school_id,group_ids,isguan')
            ->paginate($pagesize, false)->each(function ($item, $index) {
                $item['school_text'] = db('school')->where('id', $item['school_id'])->value('name');
                return $item;
            });
        $this->success('教师列表', $list);
    }

    //教师详情
    public function schoolTeacherDetail()
    {
        if (!$this->request->isGet()) $this->error('请求方式错误！');
        $id = $this->request->get('id');
        if (!$id) $this->error('未知参数！');
        $data = db('user')->where(['id' => $id])->field('id,nickname as name,mobile,email,school_id,group_ids,isguan')->find();
        if (!$data) $this->error('未找到相关信息！');
        $data['school_text'] = db('school')->where('id', $data['school_id'])->value('name');
        $list = db('user_role')->where('user_id', $data['id'])->select();
        foreach ($list as &$value) {
            $value['grade_text'] = db('school_grade')->where('id', $value['grade_id'])->value('name');
            $value['group_text'] = db('user_group')->where('id', $value['group_id'])->value('name');
            $value['gclass_text'] = db('school_class')->where('id', 'in', $value['gclass_ids'])->column('name');
            $value['elective_text'] = db('school_class')->where('id', 'in', $value['elective_ids'])->column('name');
            $value['subject_text'] = db('subject')->where('id', $value['subject_id'])->value('name');
        }
        $data['role'] = $list;
        $this->success('教师详情', $data);
    }

    //教师导出
    public function exportTeacher()
    {

        if ($this->request->isPost()) {
            $school_id = $this->school_id;
            $group_id = $this->request->post('group_id');
            $grade_id = $this->request->post('grade_id');
            $gclass_ids = $this->request->post('gclass_ids');
            $elective_ids = $this->request->post('elective_ids');
            $subject_id = $this->request->post('subject_id');
            $isguan = $this->request->post('isguan', '');

            $user = new User();
            $list = $user
                ->with([
                    'role' => function ($query) use ($grade_id, $gclass_ids, $elective_ids, $subject_id) {
                        if ($grade_id) {
                            $query->where('grade_id', $grade_id);
                        }
                        if ($gclass_ids) {
                            $query->where('find_in_set(:keywords,gclass_ids)', ['keywords' => $gclass_ids]);
                        }
                        if ($elective_ids) {
                            $query->where('find_in_set(:keywords,elective_ids)', ['keywords' => $elective_ids]);
                        }
                        if ($subject_id) {
                            $query->where('subject_id', $subject_id);
                        }
                    }
                ])
                ->where(function ($query) use ($isguan) {
                    if ($isguan && in_array($isguan, [0, 1])) {
                        $query->where('isguan', $isguan);
                    }
                })
                ->where(function ($query) use ($group_id) {
                    if ($group_id) {
                        $query->where("find_in_set({$group_id}, group_ids)");
                    }
                })
                ->where(['school_id' => $school_id, 'type' => "teacher"])
                ->field('id,nickname as name,mobile,email,school_id,group_ids,isguan')
                ->select();

            $data = [];
            #数据重组
            foreach ($list as $rows) {
                $user = $rows->toArray();
                foreach ($user['role'] as $val) {
                    $data[] = [
                        'name' => $user['name'],
                        'group_text' => $val['group_text'],
                        'grade_text' => $val['grade_text'],
                        'subject_text' => $val['subject_text'],
                        'gclass_text' => implode(',', $val['gclass_text']),
                        'elective_text' => implode(',', $val['elective_text']),
                        'mobile' => $user['mobile'],
                        'email' => $user['email']
                    ];
                }
            }

            // 加载sheet
            $spreadsheet = new Spreadsheet();
            $sheet = $spreadsheet->getActiveSheet();
            $sheet->setTitle('Sheet1');

            // 处理标题
            $title = [
                'name' => '姓名',
                'group_text' => '角色',
                'grade_text' => '年级',
                'subject_text' => '学科',
                'gclass_text' => '行政班',
                'elective_text' => '教学班',
                'mobile' => '联系电话',
                'email' => '邮箱'
            ];
            $keys = 1;
            foreach ($title as $key => $value) {
                $sheet->setCellValueByColumnAndRow($keys, 1, $value);
                $sheet->getDefaultColumnDimension()->setWidth(15);
                $keys++;
            }

            // 处理标题下内容
            // 从第二行开始
            $row = 2;
            foreach ($data as $item) {
                $column = 1;
                foreach ($title as $key => $value) {
                    $sheet->setCellValueByColumnAndRow($column, $row, $item[$key]);
                    $column++;
                }
                $row++;
            }

            // 文件下载
            $filename = './uploads/教师信息' . time() . '.xlsx';

            $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
            $writer->save('php://output');
        }
    }

    //教师导入
    public function importTeacher()
    {

        $model = new User();
        $role = new UserRole();
        $file = $this->request->post('file');
        if (!$file) {
            $this->error(__('Parameter %s can not be empty', 'file'));
        }
        $filePath = ROOT_PATH . DS . 'public' . DS . $file;
        if (!is_file($filePath)) {
            $this->error(__('No results were found'));
        }
        $school_id = $this->school_id;
        //实例化reader
        $ext = pathinfo($filePath, PATHINFO_EXTENSION);
        if (!in_array($ext, ['csv', 'xls', 'xlsx'])) {
            $this->error(__('Unknown data format'));
        }
        if ($ext === 'csv') {
            $file = fopen($filePath, 'r');
            $filePath = tempnam(sys_get_temp_dir(), 'import_csv');
            $fp = fopen($filePath, 'w');
            $n = 0;
            while ($line = fgets($file)) {
                $line = rtrim($line, "\n\r\0");
                $encoding = mb_detect_encoding($line, ['utf-8', 'gbk', 'latin1', 'big5']);
                if ($encoding !== 'utf-8') {
                    $line = mb_convert_encoding($line, 'utf-8', $encoding);
                }
                if ($n == 0 || preg_match('/^".*"$/', $line)) {
                    fwrite($fp, $line . "\n");
                } else {
                    fwrite($fp, '"' . str_replace(['"', ','], ['""', '","'], $line) . "\"\n");
                }
                $n++;
            }
            fclose($file) || fclose($fp);

            $reader = new Csv();
        } elseif ($ext === 'xls') {
            $reader = new Xls();
        } else {
            $reader = new Xlsx();
        }

        //导入文件首行类型,默认是注释,如果需要使用字段名称请使用name
        $importHeadType = isset($this->importHeadType) ? $this->importHeadType : 'comment';

        $table = $model->getQuery()->getTable();
        $database = \think\Config::get('database.database');
        $fieldArr = [];
        $roleFieldArr = [];
        $list = db()->query("SELECT COLUMN_NAME,COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ? AND TABLE_SCHEMA = ?", [$table, $database]);
        foreach ($list as $k => $v) {
            if ($importHeadType == 'comment') {
                $v['COLUMN_COMMENT'] = explode(':', $v['COLUMN_COMMENT'])[0]; //字段备注有:时截取
                $fieldArr[$v['COLUMN_COMMENT']] = $v['COLUMN_NAME'];
            } else {
                $fieldArr[$v['COLUMN_NAME']] = $v['COLUMN_NAME'];
            }
        }

        $roleTable = $role->getQuery()->getTable();
        $roleDatabase = \think\Config::get('database.database');
        $roleFieldArr = [];
        $roleList = db()->query("SELECT COLUMN_NAME,COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ? AND TABLE_SCHEMA = ?", [$roleTable, $roleDatabase]);
        foreach ($roleList as $k => $v) {
            if ($importHeadType == 'comment') {
                $v['COLUMN_COMMENT'] = explode(':', $v['COLUMN_COMMENT'])[0]; //字段备注有:时截取
                $roleFieldArr[$v['COLUMN_COMMENT']] = $v['COLUMN_NAME'];
            } else {
                $roleFieldArr[$v['COLUMN_NAME']] = $v['COLUMN_NAME'];
            }
        }

        //加载文件
        $insert = [];
        $sonInsert = [];
        try {

            if (!$PHPExcel = $reader->load($filePath)) {
                $this->error(__('Unknown data format'));
            }
            $currentSheet = $PHPExcel->getSheet(0);  //读取文件中的第一个工作表
            $allColumn = $currentSheet->getHighestDataColumn(); //取得最大的列号
            $allRow = $currentSheet->getHighestRow(); //取得一共有多少行
            $maxColumnNumber = Coordinate::columnIndexFromString($allColumn);
            $fields = [];
            for ($currentRow = 1; $currentRow <= 1; $currentRow++) {
                for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                    $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                    $fields[] = $val;
                }
            }

            for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
                $values = [];
                for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                    $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                    $values[] = is_null($val) ? '' : $val;
                }
                $row = [];
                $temp = array_combine($fields, $values);
                foreach ($temp as $k => $v) {
                    if (isset($fieldArr[$k]) && $k !== '') {
                        if ($fieldArr[$k] == 'mobile') {
                            $row['mobile'] = $v;
                        }
                        if ($fieldArr[$k] == 'nickname') {
                            $row['nickname'] = $v;
                        }
                        if ($fieldArr[$k] == 'email') {
                            $row['email'] = $v;
                        }
                    }

                    if (isset($roleFieldArr[$k]) && $k !== '') {
                        
                        if ($roleFieldArr[$k] == 'grade_id') {
                            $gradeId = db('school_grade')->where(['school_id' => $school_id, 'name' => $v])->value('id');
                            $sonRow['grade_id'] = $gradeId;
                        }
                        if ($roleFieldArr[$k] == 'gclass_ids') {
                            $ids =  db('school_class')->whereIn('name', $v)->where(['school_id' => $school_id,'grade_id'=>$sonRow['grade_id'], 'type' => 'XZ'])->column('id');
                            $sonRow['gclass_ids'] = implode(',', $ids);
                        }
                        if ($roleFieldArr[$k] == 'elective_ids') {
                            $ids =  db('school_class')->whereIn('name', $v)->where(['school_id' => $school_id, 'type' => 'JX'])->column('id');
                            $sonRow['elective_ids'] = implode(',', $ids);
                        }
                        if ($roleFieldArr[$k] == 'group_id') {
                            $groupId = db('user_group')->where(['name' => $v])->value('id');
                            $sonRow['id'] = $groupId;
                        }
                        if ($roleFieldArr[$k] == 'subject_id') {
                            $id = db('subject')->where(['name' => $v])->value('id');
                            $sonRow['subject_id'] = $id;
                        }
                    }
                }
                if ($row) {
                    $row['son'][] = $sonRow;
                    if (in_array($row['mobile'], array_column($insert, 'mobile'))) {
                        foreach ($insert as $key => &$value) {
                            if ($row['mobile'] == $value['mobile']) {
                                $value['son'] = array_merge($value['son'], $row['son']);
                            }
                        }
                    } else {
                        $insert[] = $row;
                    }
                }
            }
        } catch (Exception $exception) {
            $this->error($exception->getMessage());
        }
        
        if (!$insert) {
            $this->error(__('No rows were updated'));
        }

        $errorMessage = [];
        try {
            //是否包含admin_id字段
            foreach ($insert as $in) {
                // if ($in['email'] && !Validate::is($in['email'], "email")) {
                //     array_push($errorMessage,$in['nickname'].'导入失败，邮箱已被使用！'); 
                //     continue;
                // }
                if ($in['mobile'] && !Validate::regex($in['mobile'], "^1\d{10}$")) {
                    array_push($errorMessage,$in['nickname'].'导入失败，手机号已被使用！'); 
                    continue;
                }
                $groupIds = array_column($in['son'], 'id');
                $gids = array_filter($groupIds);
                $groupIds = implode(',', $gids);
                $res = $this->auth->registers($in['mobile'], $in['mobile'], $in['email'], $in['mobile'], $in['nickname'], ['group_ids' => $groupIds, 'school_id' => $school_id], $in['son']);
                if (!$res) {
                    array_push($errorMessage,$in['nickname'].'导入失败！'); 
                    continue;
                }
                
            }
            
            if ($errorMessage) {
                throw new Exception(implode(',',$errorMessage));
            }
            // $model->saveAll($insert);
        } catch (PDOException $exception) {
            $msg = $exception->getMessage();
            if (preg_match("/.+Integrity constraint violation: 1062 Duplicate entry '(.+)' for key '(.+)'/is", $msg, $matches)) {
                $msg = "导入失败，包含【{$matches[1]}】的记录已存在";
            };
            $this->error('错误信息!',$msg,300);
        } catch (Exception $e) {
            $this->error('错误信息!',$e->getMessage(),300);
        }

        $this->success('操作成功!');
    }


    //添加选考
    public function addSelectExam()
    {
        #select_exam
        if (!$this->request->isPost()) $this->error('请求方式错误！');
        $school_id = $this->school_id ?? 1;
        $name = $this->request->post('name');
        $tag = $this->request->post('tag');
        $subject_ids = $this->request->post('subject_ids');
        if (!$name || !$subject_ids) $this->error('参数错误');

        $classId = db('select_exam')->insertGetId([
            'school_id' => $school_id,
            'name' => $name,
            'tag' => $tag,
            'subject_ids' => $subject_ids,
        ]);
        if ($classId) {
            $this->success('添加成功！');
        } else {
            $this->error('添加失败！');
        }
    }

    //编辑选考
    public function editSelectExam()
    {
        #select_exam
        if (!$this->request->isPost()) $this->error('请求方式错误！');
        $id = $this->request->post('id');
        $name = $this->request->post('name');
        $tag = $this->request->post('tag');
        $subject_ids = $this->request->post('subject_ids');
        if (!$id || !$name || !$subject_ids) $this->error('参数错误');

        $classUp = db('select_exam')->where('id', $id)->update([
            'name' => $name,
            'tag' => $tag,
            'subject_ids' => $subject_ids,
        ]);
        if ($classUp) {
            $this->success('操作成功！');
        } else {
            $this->error('操作失败！');
        }
    }

    //删除选考
    public function delSelectExam()
    {
        if (!$this->request->isGet()) $this->error('请求方式错误！');
        $id = $this->request->get('id');
        if (!$id) $this->error('未知参数！');
        db('select_exam')->where(['id' => $id])->delete();
        $this->success('操作成功');
    }


    //选考列表
    public function selectExamList()
    {
        if (!$this->request->isGet()) $this->error('请求方式错误！');
        $school_id = $this->school_id ?? 1;
        $page = $this->request->get('page');
        $pagesize = $this->request->get('pagesize');
        if (!$page || !$pagesize) $this->error('参数错误！');
        //->where(['school_id' => $school_id])
        $list = db('select_exam')
            ->paginate($pagesize, false)->each(function ($item, $index) {
                $item['school_text'] = db('school')->where('id', $item['school_id'])->value('name');
                $item['subject_array'] = db('subject')->where('id', 'in', $item['subject_ids'])->column('name');
                return $item;
            });
        $this->success('选考列表', $list);
    }

    //选考详情
    public function selectExamDetail()
    {
        if (!$this->request->isGet()) $this->error('请求方式错误！');
        $id = $this->request->get('id');
        if (!$id) $this->error('未知参数！');
        $data = db('select_exam')->where(['id' => $id])->find();
        if (!$data) $this->error('未找到相关信息！');
        $data['school_text'] = db('school')->where('id', $data['school_id'])->value('name');

        $data['subject_array'] = db('subject')->where('id', 'in', $data['subject_ids'])->column('name');

        $this->success('选考详情', $data);
    }

    //快速选考
    public function selectExamStudent()
    {
        if (!$this->request->isPost()) $this->error('请求方式错误！');
        $school_id = $this->school_id ?? 2;
        $id = $this->request->post('id');
        $class_ids = $this->request->post('class_ids');
        $elective_ids = $this->request->post('elective_ids');
        if (!$id) $this->error('未知参数！');
        $result = db('student')
            ->where(function ($query) use ($class_ids) {
                if ($class_ids) {
                    $query->whereIn('class_id', $class_ids);
                }
            })
            ->whereOr(function ($query) use ($elective_ids) {
                if ($elective_ids) {
                    $electiveIds = explode(',', $elective_ids);
                    $sql = '';
                    foreach ($electiveIds as $key => $id) {
                        if ($key >= 1) {
                            $sql = $sql . ' or ';
                        }
                        $sql = $sql . "FIND_IN_SET($id,elective_ids)";
                    }
                    $wherec[] = ['exp', Db::raw($sql)];
                    $query->where($wherec);
                }
            })
            ->where('school_id', $school_id)
            ->update([
                'select_exam_id' => $id
            ]);
        if ($result) {
            $this->success('操作成功！');
        } else {
            $this->error('操作失败！');
        }
    }
}
